package sy.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import sy.pageModel.DataGrid;
import sy.pageModel.Room;
import sy.service.RoomServiceI;
import sy.util.ResourceUtil;
import sy.util.StringUtil;

import javax.servlet.http.HttpSession;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016-07-06.
 */
@Service
public class RoomServiceImpl implements RoomServiceI {

    @Autowired
    private JdbcTemplate template;
    @Autowired
    private HttpSession session;
    @Override
    public DataGrid findRoomDataGird(Room room) {
        DataGrid dg = new DataGrid();
        String sql = "select count(1)from p_info_proj pip, p_info pi where pi.id=pip.deptid ";
        sql = addWhere(room, sql);//模糊查询
        long total = template.queryForLong(sql);
        dg.setTotal(total);//获取总数
        sql = " select *from p_info_proj pip, p_info pi where pi.id=pip.deptid";
        sql = addWhere(room, sql);
        sql = addOrderBy(room, sql);
        sql = addLimit(room, sql);
        final List<Room> picList = new ArrayList<Room>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                Room r=new Room();
                r.setId(rs.getString("pip.id"));
                r.setDeptId(rs.getString("pi.id"));
                r.setDeptName(rs.getString("pi.name"));
                r.setName(rs.getString("pip.titles"));
                r.setUnits(rs.getString("pip.units"));
                r.setPrice(rs.getString("pip.price0"));
                r.setJdPrice(rs.getString("pip.price1"));
                r.setWhPrice(rs.getString("pip.wh_price"));
                r.setCjPrice(rs.getString("pip.cj_price"));
                r.setPayType(rs.getString("pip.paytype"));
                r.setComment(rs.getString("pip.comment"));
                r.setCreateTime(rs.getString("pip.creatd"));
                r.setCreateName(rs.getString("pip.creatu"));
                r.setCheckTime(rs.getString("pip.checkd"));
                r.setCheckName(rs.getString("pip.checku"));
                r.setPic(rs.getString("pip.pic"));
                r.setPersonNumber(rs.getInt("rnums"));
                picList.add(r);
            }});
        dg.setRows(picList);
        return dg;
    }

    @Override
    @Transactional
    public int addRoomService(Room r) {
        String createName= session.getAttribute(ResourceUtil.getSessionInfoName()).toString();
        String sql="INSERT INTO p_info_proj(id, deptid,titles, units, price0, price1, minprice, paytype, comment, creatd, creatu,pic,rnums, wh_price, cj_price) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
      int rows=  template.update(sql,new Object[]{r.getId(),r.getDeptId(),r.getName(),r.getUnits(),r.getPrice(),r.getJdPrice(),0.00,r.getPayType(),r.getComment(),StringUtil.getDate(),createName,r.getPic(),r.getPersonNumber(),r.getWhPrice(),r.getCjPrice()});
        return rows;
    }

    @Override
    @Transactional
    public int editRoomService(Room r) {
        String createName= session.getAttribute(ResourceUtil.getSessionInfoName()).toString();
        String sql="UPDATE p_info_proj SET deptid=?,titles=?, units=?, price0=?, price1=?, minprice=?, paytype=?, comment=?, checkd=?,checku=?,pic=?,rnums=?, wh_price=?, cj_price=? where id=?";
        int rows=  template.update(sql,new Object[]{r.getDeptId(),r.getName(),r.getUnits(),r.getPrice(),r.getJdPrice(),0.00,r.getPayType(),r.getComment(),StringUtil.getDate(),createName,r.getPic(),r.getPersonNumber(),r.getWhPrice(),r.getCjPrice(),r.getId()});
        return rows;
    }

    @Override
    @Transactional
    public int deleteRoomService(String id) {
        String sql="DELETE FROM p_info_proj where id=?";
        int rows=0;
        String ids[]=id.split(",");
        for (int i = 0; i <ids.length ; i++) {
            rows=template.update(sql,new Object[]{ids[i]});
        }
        return rows;
    }


    private String addLimit(Room room, String sql) {
        int fromRow = (room.getPage() - 1) * room.getRows();
        sql += " limit " + fromRow + "," + room.getRows();
        return sql;
    }

    private String addWhere(Room room, String sql) {
        if(!StringUtil.isNullOrEmpty(room.getDeptId())) {
            sql += " and pip.deptid = '" + room.getDeptId() + "'";
        }
        if(!StringUtil.isNullOrEmpty(room.getName())) {
            sql += " and pip.titles like '%" + room.getName() + "%'";
        }
        return sql;
    }


    private String addOrderBy(Room room, String sql) {
        if (room.getSort() != null) {
            if("order".equals(room.getSort())) room.setSort("pd.orderid");
            if("id".equals(room.getSort())) room.setSort("pip.id");
            if("deptName".equals(room.getSort())) room.setSort("pip.deptid");
            sql += " order by " + room.getSort() + " " + room.getOrder();
        }
        return sql;
    }
}
